﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using EvidenceManagement.Model;

namespace EvidenceManagement.Data.Common
{
    public class DataObjectCommand : IDataObjectCommand
    {

        #region Fields

        readonly IDataRepository dataRepository;
        readonly string[] dataParameterIgnoredText = null;

        #endregion

        #region Constructor

        public DataObjectCommand(IDataRepository dataRepository)
        {
            this.dataRepository = dataRepository;
            dataParameterIgnoredText = new string[] { "@", "?", "!", "#", "$", "%", "^", "&", "*", "(", ")", "[", "]", ":" };
        }

        #endregion

        #region Propeerties
        IDataRepository DataRepository
        {
            get { return dataRepository; }
        }
        #endregion

        #region Private Methods

        /// <summary>
        /// Maps the data parameters to db object data column.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbObject">The db object to map to the data parameters.</param>
        /// <param name="command">The DbCommand whose parameters to map.</param>
        /// <returns></returns>
        private bool MapDataParametersToDbObjectDataColumn<T>(T dbObject, DbCommand command)
        {
            bool hasMapped = false;

            var readableProperties = (dbObject.GetType()).GetProperties().Where(p => p.CanRead
                && p.GetCustomAttributes(typeof(DbColumn), true).Length == 1);

            var commandParameters = command.Parameters.OfType<IDataParameter>();

            foreach (var property in readableProperties)
            {
                var attribute = property.GetCustomAttributes(typeof(DbColumn), true)[0];

                if ((attribute is DbColumn))
                {
                    var dbColumn = (attribute as DbColumn);
                    string[] mappingName;

                    if (String.IsNullOrEmpty(dbColumn.MappingParameterName) == false)
                    {
                        mappingName = dbColumn.MappingParameterName.Split(dataParameterIgnoredText, StringSplitOptions.RemoveEmptyEntries);
                    }
                    else if (String.IsNullOrEmpty(dbColumn.MappingParameterName) == false)
                    {
                        mappingName = dbColumn.MappingName.Split(dataParameterIgnoredText, StringSplitOptions.RemoveEmptyEntries);
                    }
                    else
                    {
                        mappingName = property.Name.Split(dataParameterIgnoredText, StringSplitOptions.RemoveEmptyEntries);
                    }

                    if (mappingName.Length != 1)
                        continue;

                    var parameter = commandParameters.Where(p => (p.ParameterName.Split(this.dataParameterIgnoredText, StringSplitOptions.RemoveEmptyEntries)[0].Equals(mappingName[0], StringComparison.InvariantCultureIgnoreCase))).FirstOrDefault();

                    if (parameter != null)
                    {
                        (command.Parameters[parameter.ParameterName] as IDataParameter).Value = property.GetValue(dbObject, null);
                        hasMapped = true;
                    }
                }
            }
            return hasMapped;
        }

        /// <summary>
        /// Populates the data object from command resultset row.
        /// </summary>
        /// <typeparam name="T">The type of the data object to populate.</typeparam>
        /// <param name="dbObject">The db object to populate from the resultset.</param>
        /// <param name="commandResultSet">The command resultset to populate the data object with.</param>
        /// <returns></returns>
        private bool PopulateDataObjectFromCommandResultSetRow<T>(T dbObject, DataTable commandResultSet)
        {
            bool hasPopulated = false;

            if (commandResultSet != null && commandResultSet.Rows.Count == 1)
            {

                var writableProperties = (dbObject.GetType()).GetProperties().Where(p => p.CanWrite
                    && p.GetCustomAttributes(typeof(DbColumn), false).Length == 1);

                foreach (var dbObjectProperty in writableProperties)
                {
                    var attribute = dbObjectProperty.GetCustomAttributes(typeof(DbColumn), false)[0];

                    if (attribute is DbColumn)
                    {
                        var dbColumnAttribute = (attribute as DbColumn);

                        if (string.IsNullOrEmpty(dbColumnAttribute.MappingName) == true)
                        {
                            if (commandResultSet.Columns.Contains(dbObjectProperty.Name))
                            {
                                object recordValue;
                                if (DynamicallyConvertValueToSystemDataType(commandResultSet.Rows[0][dbObjectProperty.Name], dbObjectProperty.PropertyType, out recordValue))
                                {
                                    dbObjectProperty.SetValue(dbObject, recordValue, null);
                                    hasPopulated = true;
                                }
                            }
                        }
                        else
                        {
                            if (commandResultSet.Columns.Contains(dbColumnAttribute.MappingName))
                            {
                                object recordValue;
                                if (DynamicallyConvertValueToSystemDataType(commandResultSet.Rows[0][(dbColumnAttribute as DbColumn).MappingName], dbObjectProperty.PropertyType, out recordValue))
                                {
                                    dbObjectProperty.SetValue(dbObject, recordValue, null);
                                    hasPopulated = true;
                                }
                            }
                        }
                    }
                }
            }
            return hasPopulated;
        }

        /// <summary>
        /// Dynamically converts a type to the specified system type.
        /// </summary>
        /// <param name="valueToConvert">The value to convert.</param>
        /// <param name="systemTypeToChangeTo">The system type to change to.</param>
        /// <param name="convertedObject">Converted object.</param>
        /// <returns></returns>
        private bool DynamicallyConvertValueToSystemDataType(object valueToConvert, Type systemTypeToChangeTo, out object convertedObject)
        {
            var hasChanged = false;

            convertedObject = new System.ObjectDisposedException("");//set to a default non system value

            if (valueToConvert != null)
            {
                if (valueToConvert == System.DBNull.Value)
                    convertedObject = null;
                else
                {
                    var typeCode = Type.GetTypeCode(systemTypeToChangeTo);

                    switch (typeCode)
                    {
                        case TypeCode.Boolean:
                        case TypeCode.Byte:
                        case TypeCode.Char:
                        case TypeCode.DateTime:
                        case TypeCode.Decimal:
                        case TypeCode.Double:
                        case TypeCode.Empty:
                        case TypeCode.Int16:
                        case TypeCode.Int32:
                        case TypeCode.Int64:
                        case TypeCode.SByte:
                        case TypeCode.Single:
                        case TypeCode.String:
                        case TypeCode.UInt16:
                        case TypeCode.UInt32:
                        case TypeCode.UInt64:
                            convertedObject = Convert.ChangeType(valueToConvert, typeCode);
                            break;
                        case TypeCode.DBNull:
                            convertedObject = DBNull.Value;
                            break;
                        case TypeCode.Object:
                            convertedObject = (valueToConvert as Object);
                            break;
                    }

                }
                hasChanged = !(convertedObject is System.ObjectDisposedException);
            }
            return hasChanged;

        }

        /// <summary>
        /// Populates the data object list from command resultset.
        /// </summary>
        /// <typeparam name="T">The type of the data object to populate.</typeparam>
        /// <param name="dbObject">The db object to populate from the resultset</param>
        /// <param name="commandResultSet">The command resultset to populate the data object with.</param>
        /// <param name="maxCount">The maximum number of data objects to add to the return list.</param>
        /// <param name="dataObjectsList">The data objects list.</param>
        /// <returns></returns>
        private bool PopulateDataObjectListFromCommandResultSet<T>(T dbObject, DataTable commandResultSet, int maxCount, out List<T> dataObjectsList)
        {
            dataObjectsList = new List<T>();

            int maximumListSize = ((maxCount <= 0) ? int.MaxValue : maxCount);

            if (commandResultSet != null)
            {
                var dbObjectConstructor = dbObject.GetType().GetConstructor(new Type[] { });

                if (dbObjectConstructor == null)
                    return false;

                var rowCount = commandResultSet.Rows.Count;
                for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
                {
                    var resultRow = commandResultSet.Rows[rowIndex];
                    maximumListSize--;

                    dbObject = (T)(dbObjectConstructor.Invoke(null));

                    var canAddToList = false;

                    var writableDbObjectProperties = (dbObject.GetType()).GetProperties().Where(propertyInfo => propertyInfo.CanWrite);

                    foreach (var dbObjectProperty in writableDbObjectProperties)
                    {
                        foreach (var dbColumnAttribute in dbObjectProperty.GetCustomAttributes(typeof(DbColumn), false))
                        {
                            if (dbColumnAttribute is DbColumn)
                            {
                                if (string.IsNullOrEmpty((dbColumnAttribute as DbColumn).MappingName) == false)
                                {
                                    if (commandResultSet.Columns.Contains((dbColumnAttribute as DbColumn).MappingName))
                                    {
                                        var rowValue = resultRow[(dbColumnAttribute as DbColumn).MappingName];
                                        object valueToSet = null;
                                        if (DynamicallyConvertValueToSystemDataType(rowValue, dbObjectProperty.PropertyType, out valueToSet))
                                        {
                                            dbObjectProperty.SetValue(dbObject, valueToSet, null);
                                            canAddToList = true;
                                        }
                                    }
                                }
                                else
                                {
                                    if (commandResultSet.Columns.Contains(dbObjectProperty.Name))
                                    {
                                        var rowValue = resultRow[dbObjectProperty.Name];
                                        object valueToSet = null;
                                        if (DynamicallyConvertValueToSystemDataType(rowValue, dbObjectProperty.PropertyType, out valueToSet))
                                        {
                                            dbObjectProperty.SetValue(dbObject, valueToSet, null);
                                            canAddToList = true;
                                        }
                                    }
                                }
                            }
                        }
                    }

                    if (canAddToList)
                        dataObjectsList.Add(dbObject);

                    if (maximumListSize < 1)
                        break;
                }
            }
            return (dataObjectsList.Count > 0);
        }
        #endregion

        #region IDbObjectCommand Members

        /// <summary>
        /// Inserts the specified data object to the data source.
        /// </summary>
        /// <typeparam name="T">The type of the data object to insert.</typeparam>
        /// <param name="dataObject">The data object to insert.</param>
        /// <returns></returns>
        public bool Insert<T>(T dataObject)
        {
            return Insert(dataObject, null);
        }

        /// <summary>
        /// Inserts the specified data object to the data source.
        /// </summary>
        /// <typeparam name="T">The type of the data object to insert.</typeparam>
        /// <param name="dataObject">The data object to insert.</param>
        /// <param name="transaction">The database transaction to wrap the command with.</param>
        /// <returns></returns>
        public bool Insert<T>(T dataObject, DbTransaction transaction)
        {
            bool hasInserted = false;
            var customAttributes = dataObject.GetType().GetCustomAttributes(typeof(DbObject), false);

            if (customAttributes.Length == 1)//only expecting one instance of the attribute per class for typeof(Attr.DbObject)
            {
                var dbObjectAttribute = customAttributes[0];

                if (dbObjectAttribute is DbObject)
                {
                    if (String.IsNullOrEmpty((dbObjectAttribute as DbObject).InsertCommandText) == false)
                    {
                        using (var insertCommand = DataRepository.CreateCommand(CommandType.StoredProcedure, (dbObjectAttribute as DbObject).InsertCommandText))
                        {
                            DataRepository.DiscoverDbCommandParameters(insertCommand);

                            if (MapDataParametersToDbObjectDataColumn<T>(dataObject, insertCommand))
                            {
                                DataSet resultDataSet = (transaction == null) ? DataRepository.ExecuteDataSet(insertCommand)
                                    : DataRepository.ExecuteDataSet(insertCommand, transaction);

                                using (resultDataSet)
                                {
                                    if (resultDataSet.Tables.Count > 0)
                                        PopulateDataObjectFromCommandResultSetRow<T>(dataObject, resultDataSet.Tables[0]);

                                    hasInserted = true;
                                }
                            }
                        }
                    }
                    else
                        throw new InvalidDataObjectCommandException(InvalidDataObjectCommandException.DataObjectCommandType.Insert, dataObject);
                }
            }
            else
                throw new InvalidDataObjectException(dataObject);

            return hasInserted;
        }

        /// <summary>
        /// Reads the specified data object on the data source.
        /// </summary>
        /// <typeparam name="T">The type of the data object to read.</typeparam>
        /// <param name="dataObject">The data object to read into.</param>
        /// <returns></returns>
        public bool Read<T>(T dataObject)
        {
            return Read(dataObject, null);
        }

        /// <summary>
        /// Reads the specified data object on the data source.
        /// </summary>
        /// <typeparam name="T">The type of the data object to read.</typeparam>
        /// <param name="dataObject">The data object to read into.</param>
        /// <param name="transaction">The database transaction to wrap the command with.</param>
        /// <returns></returns>
        public bool Read<T>(T dataObject, DbTransaction transaction)
        {
            bool hasRead = false;
            var customAttributes = dataObject.GetType().GetCustomAttributes(typeof(DbObject), false);

            if (customAttributes.Length == 1)//only expecting one instance of the attribute per class for typeof(Attr.DbObject)
            {
                var dbObjectAttribute = customAttributes[0];

                if (dbObjectAttribute is DbObject)
                {
                    if (String.IsNullOrEmpty((dbObjectAttribute as DbObject).SelectCommandText) == false)
                    {
                        using (var readCommand = DataRepository.CreateCommand(CommandType.StoredProcedure, (dbObjectAttribute as DbObject).SelectCommandText))
                        {
                            DataRepository.DiscoverDbCommandParameters(readCommand);

                            if (MapDataParametersToDbObjectDataColumn<T>(dataObject, readCommand))
                            {
                                DataSet resultDataSet = (transaction == null) ? DataRepository.ExecuteDataSet(readCommand)
                                    : DataRepository.ExecuteDataSet(readCommand, transaction);

                                using (resultDataSet)
                                {
                                    if (resultDataSet.Tables.Count > 0
                                        && PopulateDataObjectFromCommandResultSetRow<T>(dataObject, resultDataSet.Tables[0]))
                                        hasRead = true;
                                }
                            }
                        }
                    }
                    else
                        throw new InvalidDataObjectCommandException(InvalidDataObjectCommandException.DataObjectCommandType.Read, dataObject);
                }
            }
            else
                throw new InvalidDataObjectException(dataObject);

            return hasRead;
        }

        public bool ReadRelation<T>(T dataObject)
        {
            return ReadRelation(dataObject, null);
        }

        public bool ReadRelation<T>(T dataObject, DbTransaction transaction)
        {
            var relationProperties = dataObject.GetType().GetProperties().Where(p => p.CanRead
                && p.GetCustomAttributes(typeof(DbRelation), false).Length == 1);

            foreach (var property in relationProperties)
            {
                var underlyingDbObject = property.GetValue(dataObject, null);
                Read(underlyingDbObject, transaction);
            }

            //has read
            return true;
        }

        /// <summary>
        /// Returns a generic list of the specified data object type.
        /// </summary>
        /// <typeparam name="T">The type of the data object to read</typeparam>
        /// <param name="dataObject">The data object to list.</param>
        /// <returns></returns>
        public IList<T> ReturnList<T>(T dataObject)
        {
            return ReturnList<T>(dataObject, null);
        }

        /// <summary>
        /// Returns a generic list of the specified data object type.
        /// </summary>
        /// <typeparam name="T">The type of the data object to read</typeparam>
        /// <param name="dataObject">The data object to list.</param>
        /// <param name="transaction">The database transaction to wrap the command with.</param>
        /// <returns></returns>
        public IList<T> ReturnList<T>(T dataObject, DbTransaction transaction)
        {
            return ReturnList<T>(dataObject, -1, transaction);
        }

        /// <summary>
        /// Returns a generic list of the specified data object type.
        /// </summary>
        /// <typeparam name="T">The type of the data object to read</typeparam>
        /// <param name="dataObject">The data object to list.</param>
        /// <param name="maxCount">The max number of data objects to return in the list.</param>
        /// <returns></returns>
        public IList<T> ReturnList<T>(T dataObject, int maxCount)
        {
            return ReturnList(dataObject, maxCount, null);
        }

        /// <summary>
        /// Returns a generic list of the specified data object type.
        /// </summary>
        /// <typeparam name="T">The type of the data object to read</typeparam>
        /// <param name="dataObject">The data object to list.</param>
        /// <param name="maxCount">The max number of data objects to return in the list.</param>
        /// <param name="transaction">The database transaction to wrap the command with.</param>
        /// <returns></returns>
        public IList<T> ReturnList<T>(T dataObject, int maxCount, DbTransaction transaction)
        {
            var returnList = new List<T>();
            var customAttributes = dataObject.GetType().GetCustomAttributes(typeof(DbObject), false);

            if (customAttributes.Length == 1)//only expecting one instance of the attribute per class for typeof(Attr.DbObject)
            {
                var dbObjectAttribute = customAttributes[0];

                if (dbObjectAttribute is DbObject)
                {
                    if (String.IsNullOrEmpty((dbObjectAttribute as DbObject).ListCommandText) == false)
                    {
                        using (var listCommand = DataRepository.CreateCommand(CommandType.StoredProcedure, (dbObjectAttribute as DbObject).ListCommandText))
                        {
                            DataRepository.DiscoverDbCommandParameters(listCommand);
                            MapDataParametersToDbObjectDataColumn<T>(dataObject, listCommand);

                            DataSet resultDataSet = (transaction == null) ? DataRepository.ExecuteDataSet(listCommand)
                                : DataRepository.ExecuteDataSet(listCommand, transaction);

                            using (resultDataSet)
                            {
                                if (resultDataSet.Tables.Count > 0)
                                    PopulateDataObjectListFromCommandResultSet<T>(dataObject, resultDataSet.Tables[0], maxCount, out returnList);
                            }

                            return returnList;
                        }
                    }
                    else
                        throw new InvalidDataObjectCommandException(InvalidDataObjectCommandException.DataObjectCommandType.List, dataObject);
                }
            }
            else
                throw new InvalidDataObjectException(dataObject);

            return null;
        }

        /// <summary>
        /// Updates the specified data object on the data source.
        /// </summary>
        /// <typeparam name="T">The type of the data object to update.</typeparam>
        /// <param name="dataObject">The data object to update.</param>
        /// <returns></returns>
        public bool Update<T>(T dataObject)
        {
            return Update(dataObject, null);
        }

        /// <summary>
        /// Updates the specified data object on the data source.
        /// </summary>
        /// <typeparam name="T">The type of the data object to update.</typeparam>
        /// <param name="dataObject">The data object to update.</param>
        /// <param name="transaction">The database transaction to wrap the command with.</param>
        /// <returns></returns>
        public bool Update<T>(T dataObject, DbTransaction transaction)
        {
            bool hasUpdated = false;

            var customAttributes = dataObject.GetType().GetCustomAttributes(typeof(DbObject), false);

            if (customAttributes.Length == 1)//only expecting one instance of the attribute per class for typeof(Attr.DbObject)
            {
                var dbObjectAttribute = customAttributes[0];

                if (dbObjectAttribute is DbObject)
                {
                    if (String.IsNullOrEmpty((dbObjectAttribute as DbObject).UpdateCommandText) == false)
                    {
                        using (var updateCommand = DataRepository.CreateCommand(CommandType.StoredProcedure
                            , (dbObjectAttribute as DbObject).UpdateCommandText))
                        {

                            DataRepository.DiscoverDbCommandParameters(updateCommand);

                            if (MapDataParametersToDbObjectDataColumn<T>(dataObject, updateCommand))
                            {
                                using (var resultDataSet = DataRepository.ExecuteDataSet(updateCommand))
                                {
                                    if (resultDataSet.Tables.Count > 0)
                                        PopulateDataObjectFromCommandResultSetRow<T>(dataObject, resultDataSet.Tables[0]);

                                    hasUpdated = true;
                                }
                            }
                        }
                    }
                    else
                        throw new InvalidDataObjectCommandException(InvalidDataObjectCommandException.DataObjectCommandType.Update, dataObject);
                }
            }
            else
                throw new InvalidDataObjectException(dataObject);

            return hasUpdated;
        }

        /// <summary>
        /// Deletes the specified data object on the data source.
        /// </summary>
        /// <typeparam name="T">The type of the data object to delete.</typeparam>
        /// <param name="dataObject">The data object to delete.</param>
        /// <returns></returns>
        public bool Delete<T>(T dataObject)
        {
            return Delete(dataObject, null);
        }

        public bool Delete<T>(T dataObject, DbTransaction transaction)
        {
            bool hasDeleted = false;
            var customAttributes = dataObject.GetType().GetCustomAttributes(typeof(DbObject), false);

            if (customAttributes.Length == 1)//only expecting one instance of the attribute per class for typeof(Attr.DbObject)
            {
                var dbObjectAttribute = customAttributes[0];

                if (dbObjectAttribute is DbObject)
                {
                    if (String.IsNullOrEmpty((dbObjectAttribute as DbObject).DeleteCommandText) == false)
                    {
                        using (var deleteCommand = DataRepository.CreateCommand(CommandType.StoredProcedure
                            , (dbObjectAttribute as DbObject).DeleteCommandText))
                        {
                            DataRepository.DiscoverDbCommandParameters(deleteCommand);
                            if (MapDataParametersToDbObjectDataColumn<T>(dataObject, deleteCommand))
                            {
                                DataSet resultDataSet = (transaction == null) ? DataRepository.ExecuteDataSet(deleteCommand)
                                    : DataRepository.ExecuteDataSet(deleteCommand, transaction);

                                using (resultDataSet)
                                {
                                    if (resultDataSet.Tables.Count > 0)
                                        PopulateDataObjectFromCommandResultSetRow<T>(dataObject, resultDataSet.Tables[0]);
                                }
                                hasDeleted = true;
                            }
                        }
                    }
                    else
                        throw new InvalidDataObjectCommandException(InvalidDataObjectCommandException.DataObjectCommandType.Delete, dataObject);
                }
            }
            else
                throw new InvalidDataObjectException(dataObject);

            return hasDeleted;
        }

        #endregion
    }
}
